package es.aa.android.cartomatic.implementations.da;


public class SQLiteConstants {

    // DataBase
    public final static String NAME = "carto_matic";
    public final static int VERSION = 1;

    // Tables
    public final static String TABLE_CARDS = "cards";
    public final static String TABLE_DECKS = "decks";
    public final static String TABLE_CARD_BELONG = "cards_belong";

    // Fields
    public final static String FIELD_ID = "id";
    public final static String FIELD_NAME = "name";
    public final static String FIELD_DESC = "description";
    public final static String FIELD_IMAGE = "image";
    public final static String FIELD_ID_CARD = "id_card";
    public final static String FIELD_ID_DECK = "id_deck";

    // Querys
    public final static String CREATE_CARD = "CREATE TABLE IF NOT EXISTS cards (id INT PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT, description TEXT, image BLOB)";
    public final static String CREATE_DECK = "CREATE TABLE IF NOT EXISTS decks (id INT PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT, description TEXT, image BLOB)";
    public final static String CREATE_CARD_BELONG = "CREATE TABLE IF NOT EXISTS cards_belong (id_deck INT , id_card INT, num_cards INT)";

    public final static String DELETE_CARDS = "DELELE TABLE IF EXISTS cards";
    public final static String DELETE_DECKS = "DELETE TABLE IF EXISTS decks";
    public final static String DELETE_CARD_BELONG = "DELETE TABLE IF EXISTS cards_belong";
    public final static String DELETE_CARD_IS_OF_DECK = "DELETE FROM cards_belong WHERE id_deck = ? and id_card = ?";
    
    
    public final static String INSERT_CARD = "INSERT INTO cards (id, name, description, image) VALUES (?, ?, ?, ?)";
    public final static String INSERT_DECK = "INSERT INTO decks (id, name, description, image) VALUES (?, ?, ?, ?)";
    public final static String INSERT_CARD_BELONG = "INSERT INTO cards_belong (id_deck, id_card, num_cards) VALUES (?, ?, ?)";

    public final static String SELECT_ALL_CARDS = "SELECT id, name, description, image FROM cards";
    public final static String SELECT_ALL_DECKS = "SELECT id, name, description, image FROM decks";
    public final static String SELECT_DECKS_FOR_OPTIONS = "SELECT id, name FROM decks";

    public final static String SELECT_CARD = "SELECT id, name, description, image FROM cards WHERE id = ?";
    public final static String SELECT_DECK = "SELECT id, name, description, image FROM decks WHERE id = ?";
    public final static String SELECT_CARDS_ID_OF_DECK = "SELECT id_card, num_cards FROM card_belong WHERE id_deck = ?";
    public final static String SELECT_CARDS_ON_DECK = "SELECT num_cards FROM card_belong WHERE id_deck = ? AND id_card = ?";
    
    public final static String UPDATE_SUM_NUM_OF_CARDS = "UPDATE cards_belong SET num_cards= num_cards+1 WHERE id_deck = ? and id_card = ?";
    public final static String UPDATE_SUBTRACT_NUM_OF_CARDS = "UPDATE cards_belong SET num_cards= num_cards-1 WHERE id_deck = ? and id_card = ?";
    
}
